﻿using Newtonsoft.Json;
using NPOI.HSSF.UserModel;
using PSI.Helper;
using PSI.Models.TMS;
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;

namespace PSI.Services.TMS {
    public class OutConsignationService : PSIBaseService {

        public List<OutConsignation> GetOutConsignationListData(NameValueCollection formData) {
            var result = new List<OutConsignation>();
            var billNumber = formData["billNumber"];
            var dt1 = formData["dt1"];
            var dt2 = formData["dt2"];

            //var colList = new List<string> { "id", "f_bill_num", 
            //"f_biz_man", "f_biz_dt", "f_take_cnt_dt", "f_clients", "f_status",
            //"f_confirm_user_name", "f_confirm_dt", "f_plan_act_dt"};

            var sb = new StringBuilder();
            sb.Append("select id,f_bill_num,f_biz_dt,f_take_cnt_dt,f_clients,f_status,f_confirm_user_name,f_confirm_dt,f_plan_act_dt,f_biz_man,");
            sb.Append("stuff(( select distinct','+ c.f_address from tms_out_consignation_detail c where c.parent_id=z.id  for xml path('')),1,1,'') f_address,");
            sb.Append("stuff(( select distinct','+ c.f_cnt_owner from tms_out_consignation_detail c where c.parent_id=z.id  for xml path('')),1,1,'') f_cnt_owner,");
            sb.Append("stuff(( select distinct','+ c.f_cnt_type_1 from tms_out_consignation_detail c where c.parent_id=z.id  for xml path('')),1,1,'') f_cnt_type,");
            sb.Append("stuff(( select distinct','+ c.f_goods from tms_out_consignation_detail c where c.parent_id=z.id  for xml path('')),1,1,'') f_goods,");
            sb.Append("stuff(( select distinct','+ c.f_ton from tms_out_consignation_detail c where c.parent_id=z.id  for xml path('')),1,1,'') f_ton,");
            sb.Append("(select COUNT(*) from tms_out_consignation_detail c where c.parent_id=z.id) as f_cnt_num,");
            sb.Append("f_LoadMatter,f_SpecialMatter,f_StarPort,f_Psyxj from tms_out_consignation z");

            // var indexId = colList.IndexOf("id");
            //var indexBillNumber = colList.IndexOf("f_bill_num");
            //var indexVsl = colList.IndexOf("f_vsl");
            //var indexVoyNumber = colList.IndexOf("f_voy_num");
            //var indexBizMan = colList.IndexOf("f_biz_man");
            //var indexBizDT = colList.IndexOf("f_biz_dt");
            //var indexTackCntDT = colList.IndexOf("f_take_cnt_dt");
            //var indexClients = colList.IndexOf("f_clients");
            //var indexStatus = colList.IndexOf("f_status");
            //var indexConfirmUserName = colList.IndexOf("f_confirm_user_name");
            //var indexConfirmDT = colList.IndexOf("f_confirm_dt");
            //var indexPlanActDt = colList.IndexOf("f_plan_act_dt");


            var p = CreateDbParameters();
            if (string.IsNullOrEmpty(billNumber) && string.IsNullOrEmpty(dt1) && string.IsNullOrEmpty(dt2)) {
                // 没有查询条件的时候，只显示最近三天的委托
                sb.Append(" where create_datetime between DATEADD(DAY, -3, GETDATE()) and GETDATE()");
            } else {
                sb.Append(" where (1 = 1) ");
                if (!string.IsNullOrEmpty(billNumber)) {
                    // 按单号查询
                    sb.Append(" and (f_bill_num like @billNumber) ");
                    p.AddWithValue("billNumber", "%" + billNumber + "%");
                }

                if (!string.IsNullOrEmpty(dt1) || !string.IsNullOrEmpty(dt2)) {
                    if (string.IsNullOrEmpty(dt1)) {
                        dt1 = dt2;
                    }
                    if (string.IsNullOrEmpty(dt2)) {
                        dt2 = dt1;
                    }
                    sb.Append(" and (convert(date, f_biz_dt) between @dt1 and @dt2)");
                    p.AddWithValue("dt1", dt1);
                    p.AddWithValue("dt2", dt2);
                }

            }
            sb.Append(" order by f_biz_dt desc, f_bill_num desc");

            var sql = sb.ToString();
            Query(sql, p, reader => {
                var ic = new OutConsignation {
                    Id = reader.GetString(reader.GetOrdinal("id")),
                    BillNumber = reader.GetString(reader.GetOrdinal("f_bill_num")),
                    Address = reader.GetString(reader.GetOrdinal("f_address")),
                    LoadMatter = reader.GetString(reader.GetOrdinal("f_LoadMatter")),
                    SpecialMatter = reader.GetString(reader.GetOrdinal("f_SpecialMatter")),
                    Psyxj = reader.GetString(reader.GetOrdinal("f_Psyxj")),
                    StarPort = reader.GetString(reader.GetOrdinal("f_StarPort")),
                    Cnt = reader.GetString(reader.GetOrdinal("f_ton")),

                    CntType = reader.GetString(reader.GetOrdinal("f_cnt_type")),
                    CntOwner = reader.GetString(reader.GetOrdinal("f_cnt_owner")),
                    CntNum = reader.GetInt32(reader.GetOrdinal("f_cnt_num")),
                    GoodName = reader.GetString(reader.GetOrdinal("f_goods")),

                    BizMan = reader.GetString(reader.GetOrdinal("f_biz_man")),
                    Clients = reader.GetString(reader.GetOrdinal("f_clients")),
                    Status = reader.GetString(reader.GetOrdinal("f_status")),
                    ConfrimUserName = reader.GetString(reader.GetOrdinal("f_confirm_user_name"))
                };

                //var voyNumber = reader.GetString(indexVoyNumber);
                //if (!string.IsNullOrEmpty(voyNumber)) {
                //    ic.Vsl = ic.Vsl + "/" + voyNumber;
                //}

                if (!reader.IsDBNull(reader.GetOrdinal("f_biz_dt")))
                {
                    ic.BizDT = reader.GetDateTime(reader.GetOrdinal("f_biz_dt")).ToYmd();
                }
                if (!reader.IsDBNull(reader.GetOrdinal("f_take_cnt_dt")))
                {
                    ic.TakeCntDT = reader.GetDateTime(reader.GetOrdinal("f_take_cnt_dt")).ToYmdHm();
                }
                if (!reader.IsDBNull(reader.GetOrdinal("f_confirm_dt")))
                {
                    ic.ConfirmDT = reader.GetDateTime(reader.GetOrdinal("f_confirm_dt")).ToYmdHm();
                }
                if (!reader.IsDBNull(reader.GetOrdinal("f_plan_act_dt")))
                {
                    ic.ActionDT = reader.GetDateTime(reader.GetOrdinal("f_plan_act_dt")).ToYmd();
                }
                result.Add(ic);
            });

            // 最多返回100条记录
            if (result.Count > 100) {
                return (from item in result select item).Take(100).ToList();
            } else {
                return result;
            }
        }

        public List<OutConsignationDetail> GetOutConsignationDetailListData(string parentId) {
            var result = new List<OutConsignationDetail>();

            var colList = new List<string> { "id","parent_id","f_bl_number", "f_vsl", "f_voy_num", "f_address", "f_goods_owner",
            "f_goods_owner_tel", "f_memo", "f_cnt_owner", "f_goods", 
            "f_load_cnt_man", "f_load_cnt_man_tel", "f_ton", "f_dest_port","i_isweixin",
            "f_status","f_plan_act_dt"};
            var indexId = colList.IndexOf("id");
            var indexBlNumber = colList.IndexOf("f_bl_number");
            var indexVsl = colList.IndexOf("f_vsl");
            var indexVoyNumber = colList.IndexOf("f_voy_num");
            var indexAddress = colList.IndexOf("f_address");
            var indexGoodsOwner = colList.IndexOf("f_goods_owner");
            var indexGoodsOwnerTel = colList.IndexOf("f_goods_owner_tel");
            var indexMemo = colList.IndexOf("f_memo");
            var indexCntOwner = colList.IndexOf("f_cnt_owner");
            var indexGoods = colList.IndexOf("f_goods");
            var indexLoadCntMan = colList.IndexOf("f_load_cnt_man");
            var indexLoadCntManTel = colList.IndexOf("f_load_cnt_man_tel");
            var indexTon = colList.IndexOf("f_ton");
            var indexDestPort = colList.IndexOf("f_dest_port");
            var indexStatus = colList.IndexOf("f_status");

            var where = parentId == "" ? "1=1" : "parent_id = @parentId";

            var sql = "select " + colList.CombineWithComma()
                + " from tms_out_consignation_detail where "+where+" order by show_order";
            var p = CreateDbParameters();
            if(parentId!="")
               p.AddWithValue("parentId", parentId);
            Query(sql, p, reader => {
                var ocd = new OutConsignationDetail {
                    Id = reader.GetString(indexId),
                    ParentId = reader.GetString(reader.GetOrdinal("parent_id")),
                    BlNumber = reader.GetString(indexBlNumber),
                    Vsl = reader.GetString(indexVsl),
                    VoyNumber = reader.GetString(indexVoyNumber),
                    Address = reader.GetString(indexAddress),
                    GoodsOwner = reader.GetString(indexGoodsOwner),
                    GoodsOwnerTel = reader.GetString(indexGoodsOwnerTel),
                    Memo = reader.GetString(indexMemo),
                    CntOwner = reader.GetString(indexCntOwner),
                    Goods = reader.GetString(indexGoods),
                    LoadCntMan = reader.GetString(indexLoadCntMan),
                    LoadCntManTel = reader.GetString(indexLoadCntManTel),
                    Ton = reader.GetString(indexTon),
                    DestPort = reader.GetString(indexDestPort),
                    PlanDt = reader.GetDateTime(reader.GetOrdinal("f_plan_act_dt")),
                    isweixin = (IsWXStatus)Enum.Parse(typeof(IsWXStatus), reader.GetInt32(reader.GetOrdinal("i_isweixin")).ToString()),
                    Status = reader.GetString(indexStatus)
                };
                var voyNumber = reader.GetString(indexVoyNumber);
                if (!string.IsNullOrEmpty(voyNumber))
                {
                    ocd.Vsl = ocd.Vsl + "/" + voyNumber;
                }
                result.Add(ocd);
            });

            return result;
        }

        public Result EditOutConsignation(NameValueCollection formData) {
            var id = formData["id"]; 
            var clients = formData["clients"];
            var takeCntDate = formData["takeCntDate"];
            var takeCntTime = formData["takeCntTime"];
            var bizMan = formData["bizMan"];
            var bizDT = formData["bizDT"];
            var userId = UserService.GetLoginUser().Id;

            if (string.IsNullOrEmpty(id)) {
                // 新增
                WithTransaction(() => {
                    var bns = new BillNumberService();
                    var mid = string.Format("{0:yyMMdd}", DateTime.Now);
                    var billNumber = bns.GetAutoNumber("出口委托", "CK", mid);
                    id = IdGeneratorService.NewId();

                    var sql = @"insert into tms_out_consignation(id, f_bill_num, f_take_cnt_dt, f_clients,
                                    f_biz_man, f_biz_dt, create_user_id, create_datetime)
                                values(@id, @billNumber,@takeCntDT, @clients, @bizMan, @bizDT, @userId, getdate())";

                    var p = CreateDbParameters();
                    p.AddWithValue("id", id);
                    p.AddWithValue("billNumber", billNumber);
                    p.AddWithValue("takeCntDT", takeCntDate + " " + takeCntTime);
                    p.AddWithValue("clients", clients);
                    p.AddWithValue("bizMan", bizMan);
                    p.AddWithValue("bizDT", bizDT);
                    p.AddWithValue("userId", userId);

                    Execute(sql, p);

                    new LogService().Log("新增出口委托，单号 = " + billNumber);
                });
            } else {
                // 编辑
                WithTransaction(() => {
                    var sql = @"update tms_out_consignation
                                set f_take_cnt_dt = @takeCntDT, f_clients = @clients,
                                    f_biz_man = @bizMan, f_biz_dt = @bizDT,
                                    update_user_id = @userId, update_datetime = getdate()
                                where id = @id";
                    var p = CreateDbParameters();
                    p.AddWithValue("id", id);
                    p.AddWithValue("takeCntDT", takeCntDate + " " + takeCntTime);
                    p.AddWithValue("clients", clients);
                    p.AddWithValue("bizMan", bizMan);
                    p.AddWithValue("bizDT", bizDT);
                    p.AddWithValue("userId", userId);
                    Execute(sql, p);

                    new LogService().Log("编辑出口委托, 单号 = " + formData["billNumber"]);
                });
            }

            return new Result { Success = true, Id = id };

        }

        public OutConsignation GetOutConsignation(string id) {
            var result = new OutConsignation { Id = id };

            var colList = new List<string> { "f_bill_num", "f_clients", "f_take_cnt_dt", "f_biz_man", "f_biz_dt"};
            
            var indexBillNumber = colList.IndexOf("f_bill_num");
            //var indexVsl = colList.IndexOf("f_vsl");
            //var indexVoyNumber = colList.IndexOf("f_voy_num");
            var indexClients = colList.IndexOf("f_clients");
            var indexTakeCntDT = colList.IndexOf("f_take_cnt_dt");
            var indexBizMan = colList.IndexOf("f_biz_man");
            var indexBizDT = colList.IndexOf("f_biz_dt");

            var sql = @"select " + colList.CombineWithComma()
                + " from tms_out_consignation where id = @id";
            var p = CreateDbParameters();
            p.AddWithValue("id", id);

            Query(sql, p, reader => {
                result.BillNumber = reader.GetString(indexBillNumber);
                //result.Vsl = reader.GetString(indexVsl);
                //result.VoyNumber = reader.GetString(indexVoyNumber);
                result.Clients = reader.GetString(indexClients);
                result.TakeCntDate = reader.GetDateTime(indexTakeCntDT).ToYmd();
                result.TakeCntTime = reader.GetDateTime(4).ToHm();
                result.BizMan = reader.GetString(indexBizMan);
                result.BizDT = reader.GetDateTime(indexBizDT).ToYmd();
            });

            return result;
        }

        public Result EditOutConsignationDetail(NameValueCollection formData) {
            var id = formData["id"];
            var parentId = formData["parentId"];
            var blNumber = formData["blNumber"];
            var vsl = formData["vsl"];
            var voynum = formData["voyNumber"];
            var address = formData["address"];
            var goodsOwner = formData["goodsOwner"];
            var goodsOwnerTel = formData["goodsOwnerTel"];
            var memo = formData["memo"];
            var cntOwner = formData["cntOwner"];
            var goods = formData["goods"];
            var loadCntMan = formData["loadCntMan"];
            var loadCntManTel = formData["loadCntManTel"];
            var ton = formData["ton"];
            var destPort = formData["destPort"];

            var userId = UserService.GetLoginUser().Id;

            if (string.IsNullOrEmpty(id)) {
                // 新增
                var truckCountStr = formData["truckCount"];
                int truckCount;
                if (!int.TryParse(truckCountStr, out truckCount)) {
                    truckCount = 1;
                }

                WithTransaction(() => {
                    for (int i = 0; i < truckCount; i++) {
                        // 批量新增送货明细
                        id = IdGeneratorService.NewId();

                        var sql = @"insert into tms_out_consignation_detail(id, parent_id, 
                        f_bl_number,f_vsl,f_voy_num,f_address, f_goods_owner, f_goods_owner_tel, f_goods, 
                        f_cnt_owner, f_load_cnt_man, f_load_cnt_man_tel,
                        f_ton, f_dest_port,
                        f_memo, create_user_id, create_datetime)
                        values (@id, @parentId, @blNumber,@vsl,@voynum,
                            @address, @goodsOwner, @goodsOwnerTel, @goods, 
                            @cntOwner, @loadCntMan, @loadCntManTel,
                            @ton, @destPort,
                            @memo, @userId, getdate())";
                        var p = CreateDbParameters();
                        p.AddWithValue("id", id);
                        p.AddWithValue("parentId", parentId);
                        p.AddWithValue("blNumber", blNumber);
                        p.AddWithValue("vsl",vsl);
                        p.AddWithValue("voynum", voynum);
                        p.AddWithValue("address", address);
                        p.AddWithValue("goodsOwner", goodsOwner);
                        p.AddWithValue("goodsOwnerTel", goodsOwnerTel);
                        p.AddWithValue("goods", goods);
                        p.AddWithValue("cntOwner", cntOwner);
                        p.AddWithValue("loadCntMan", loadCntMan);
                        p.AddWithValue("loadCntManTel", loadCntManTel);
                        p.AddWithValue("memo", memo);
                        p.AddWithValue("userId", userId);
                        p.AddWithValue("ton", ton);
                        p.AddWithValue("destPort", destPort);

                        Execute(sql, p);
                    }
                });
            } else {
                // 编辑
                WithTransaction(() => {
                    var sql = @"update tms_out_consignation_detail
                                set f_bl_number = @blNumber,f_vsl=@vsl,f_voy_num=@voynum,f_address = @address,    
                                    f_goods_owner = @goodsOwner, f_goods_owner_tel = @goodsOwnerTel,
                                    f_ton = @ton, f_dest_port = @destPort,
                                    f_memo = @memo, update_user_id = @userId, update_datetime = getdate(),
                                    f_cnt_owner = @cntOwner, f_goods = @goods,
                                    f_load_cnt_man = @loadCntMan, f_load_cnt_man_tel = @loadCntManTel
                                where id = @id";
                    var p = CreateDbParameters();
                    p.AddWithValue("id", id);
                    p.AddWithValue("blNumber", blNumber);
                    p.AddWithValue("vsl", vsl);
                    p.AddWithValue("voynum", voynum);
                    p.AddWithValue("address", address);
                    p.AddWithValue("goodsOwner", goodsOwner);
                    p.AddWithValue("goodsOwnerTel", goodsOwnerTel);
                    p.AddWithValue("memo", memo);
                    p.AddWithValue("userId", userId);
                    p.AddWithValue("cntOwner", cntOwner);
                    p.AddWithValue("goods", goods);
                    p.AddWithValue("loadCntMan", loadCntMan);
                    p.AddWithValue("loadCntManTel", loadCntManTel);

                    p.AddWithValue("ton", ton);
                    p.AddWithValue("destPort", destPort);

                    Execute(sql, p);
                });
            }

            return new Result { Success = true };
        }

        public OutConsignationDetail GetOutConsignationDetail(string id) {
            var result = new OutConsignationDetail {
                Id = id
            };
            var colList = new List<string> { "f_bl_number", "f_address", "f_goods_owner",
            "f_goods_owner_tel", "f_memo", "f_cnt_owner", "f_goods", "f_load_cnt_man","f_status",
            "f_load_cnt_man_tel", "f_ton", "f_dest_port","f_truck_num","i_isweixin"};
            var indexBlNumber = colList.IndexOf("f_bl_number");
            var indexAddress = colList.IndexOf("f_address");
            var indexGoodsOwner = colList.IndexOf("f_goods_owner");
            var indexGoodsOwnerTel = colList.IndexOf("f_goods_owner_tel");
            var indexMemo = colList.IndexOf("f_memo");
            var indexCntOwner = colList.IndexOf("f_cnt_owner");
            var indexGoods = colList.IndexOf("f_goods");
            var indexLoadCntMan = colList.IndexOf("f_load_cnt_man");
            var indexLoadCntManTel = colList.IndexOf("f_load_cnt_man_tel");
            var indexTon = colList.IndexOf("f_ton");
            var indexStatus = colList.IndexOf("f_status");
            var indexDestPort = colList.IndexOf("f_dest_port");
            var isweixin = colList.IndexOf("i_isweixin");

            var sql = @"select " + colList.CombineWithComma() 
                + " from tms_out_consignation_detail where id = @id";
            var p = CreateDbParameters();
            p.AddWithValue("id", id);
            Query(sql, p, reader => {
                result.BlNumber = reader.GetString(indexBlNumber);
                result.Address = reader.GetString(indexAddress);
                result.GoodsOwner = reader.GetString(indexGoodsOwner);
                result.GoodsOwnerTel = reader.GetString(indexGoodsOwnerTel);
                result.Memo = reader.GetString(indexMemo);
                result.CntOwner = reader.GetString(indexCntOwner);
                result.Goods = reader.GetString(indexGoods);
                result.LoadCntMan = reader.GetString(indexLoadCntMan);
                result.LoadCntManTel = reader.GetString(indexLoadCntManTel);
                result.Ton = reader.GetString(indexTon);
                result.truckNum = reader.GetString(reader.GetOrdinal("f_truck_num"));
                result.Status = reader.GetString(indexStatus);
                result.DestPort = reader.GetString(indexDestPort);
                result.isweixin = (IsWXStatus)Enum.Parse(typeof(IsWXStatus), reader.GetInt32(isweixin).ToString());
            });

            return result;
        }

        public Result DeleteOutConsignationDetail(string idJson, string parentId) {
            var idList = JsonConvert.DeserializeObject<List<string>>(idJson);

            // 检查能否删除
            foreach (var id in idList) {
                var sql = "select f_status from tms_out_consignation_detail where id = @id";
                var p = CreateDbParameters();
                p.AddWithValue("id", id);
                var status = string.Empty;
                Query(sql, p, reader => {
                    status = reader.GetString(0);
                });
                if (!string.IsNullOrEmpty(status) && status != "待下达计划") {
                    return new Result { Success = false, Messgae = "送货明细状态为[" + status + "]，不能删除" };
                }
            }

            WithTransaction(() => {
                foreach (var id in idList) {
                    var sql = @"
                            delete from tms_out_consignation_detail where id = @id
                        ";
                    var p = CreateDbParameters();
                    p.AddWithValue("id", id);

                    Execute(sql, p);
                }
            });

            return new Result { Success = true };
        }

        public Result DeleteOutConsignation(string idJson) {
            var idList = JsonConvert.DeserializeObject<List<string>>(idJson);

            // 检查能否删除
            foreach (var id in idList) {
                var sql = "select f_status from tms_out_consignation where id = @id";
                var p = CreateDbParameters();
                p.AddWithValue("id", id);
                var status = string.Empty;
                Query(sql, p, reader => {
                    status = reader.GetString(0);
                });
                if (!string.IsNullOrEmpty(status)) {
                    return new Result { Success = false, Messgae = "委托已经被确认运输，不能删除" };
                }
            }

            WithTransaction(() => {
                foreach (var id in idList) {
                    var sql = @"
                            delete from tms_out_consignation_detail where parent_id = @id
                            delete from tms_out_consignation where id  = @id
                        ";
                    var p = CreateDbParameters();
                    p.AddWithValue("id", id);

                    Execute(sql, p);

                    new LogService().Log("删除出口委托, id = " + id);
                }
            });

            return new Result { Success = true };
        }

        public Result MakePlan(string id, string actionDate, string isweixin, string dispatcher)
        {
            var userId = UserService.GetLoginUser().Id;
            var user = new UserService().GetUser(userId);

            WithTransaction(() => {
                var sql = @"update tms_out_consignation
                            set f_status = '已确认运输', f_confirm_user_name = @userName,
                                f_confirm_dt = getdate(), f_plan_act_dt = @actionDate,i_isweixin=@isweixin 
                            where id = @id

                            update tms_out_consignation_detail
                            set f_status = '待下达计划', f_plan_act_dt = @actionDate,i_isweixin=@isweixin 
                            where parent_id = @id
                            ";
                var p = CreateDbParameters();
                p.AddWithValue("id", id);
                p.AddWithValue("userName", user.Name);
                p.AddWithValue("actionDate", actionDate);
                p.AddWithValue("isweixin", string.IsNullOrEmpty(isweixin) ? IsWXStatus.notrelease : IsWXStatus.release);
                Execute(sql, p);
                if (!string.IsNullOrEmpty(dispatcher))
                {
                    var sqls = @"update tms_out_consignation_detail
                                set f_dispatcher = @dispatcher, f_plan_dt = getdate(), f_plan_num = @planNumber,
                                    f_plan_flag = 0, f_status = '待派车'
                                where parent_id = @id and f_plan_dt is null
                            ";
                    var pp = CreateDbParameters();
                    var planNumber =new OutPlanService().GetAutoNumber();

                    pp.AddWithValue("id", id);
                    pp.AddWithValue("dispatcher", dispatcher);
                    pp.AddWithValue("planNumber", planNumber);
                    Execute(sqls, pp);
                }

                new LogService().Log("对出口委托确认运输，id = " + id);
            });

            return new Result { Success = true };
        }

        public string Upload(string fileName) {
            var workbook = new HSSFWorkbook(new FileStream(fileName, FileMode.Open));
            #region 得到两个页的行和列

                var sheet = workbook.GetSheetAt(0);
                var rowCount = sheet.PhysicalNumberOfRows;
                var ros = sheet.LastRowNum;
                var colCount = sheet.GetRow(0).PhysicalNumberOfCells;

                var NextSheet = workbook.GetSheetAt(1);
                var NextrowCount = NextSheet.PhysicalNumberOfRows;
                var NextcolCount = NextSheet.GetRow(0).PhysicalNumberOfCells;

            #endregion 

            #region 确认各列对应的数据

                #region 第一页行索引
                    var indexTakeCntDT = -1;       //装货日期，装箱时限
                    var indexClients = -1;         //托运人，委托方
                    var indexAddress = -1;         //装货地点
                    var indexBoxAttribute = -1;    //箱属
                    var indexBoxType = -1;         //箱型
                    var indexBoxCount = -1;        //箱量
                    var indexGoods = -1;           //货名
                    var indexGoodsWeight = -1;     //货重
                    var indexLoadMatter = -1;      //装货事项
                    var indexSpecialMatter = -1;   //特殊事项
                    var indexStarPort = -1;        //起运港
                    var indexPsyxj = -1;           //配送优先级
                    
               for (int col = 0; col < colCount+1; col++) {
                    var cell = sheet.GetRow(0).GetCell(col);
                    var colTitle = cell==null ? "" : cell.ToString();
                    if (colTitle == "装货日期") {
                        indexTakeCntDT = col;
                    }
                    if (colTitle == "托运人") {
                        indexClients = col;
                    }
                    if (colTitle == "装货地点") {
                        indexAddress = col;
                    }
                    if (colTitle == "箱属") {
                        indexBoxAttribute = col;
                    }
                    if (colTitle == "箱型") {
                        indexBoxType = col;
                    }
                    if (colTitle == "箱量") {
                        indexBoxCount = col;
                    }
                    if (colTitle == "货名") {
                        indexGoods = col;
                    }
                    if (colTitle == "货重") {
                        indexGoodsWeight = col;
                    }
                    if (colTitle == "装货事项") {
                        indexLoadMatter = col;
                    }
                    if (colTitle == "特殊事项") {
                        indexSpecialMatter = col;
                    }
                    if (colTitle == "起运港")
                    {
                        indexStarPort = col;
                    }
                    if (colTitle == "配送优先级")
                    {
                        indexPsyxj = col;
                    }
                 
                }
                #endregion

                #region 第二页行索引
                    var indexTakeCntDT_Next = -1;       //装货日期，装箱时限
                    var indexClients_Next = -1;         //托运人，委托方
                    var indexAddress_Next = -1;         //装货地点
                    var indexVsl_Next = -1;             //船名
                    var indexVoyNumber_Next = -1;       //航次
                    var indexBL_Next = -1;              //运单号
                    var indexBoxAttribute_Next = -1;    //箱属
                    var indexBoxType_Next = -1;         //箱型
                    var indexBoxCount_Next = -1;        //箱量
                    var indexGoods_Next = -1;           //货名
                    var indexGoodsWeight_Next = -1;     //货重
                    var indexLoadMatter_Next = -1;      //装货事项
                    var indexSpecialMatter_Next = -1;   //特殊事项
                    var indexPsyxj_Next = -1;           //配送优先级

                    for (int col = 0; col < NextcolCount; col++)
                    {
                        var cell = NextSheet.GetRow(0).GetCell(col);
                        var colTitle = cell == null ? "" : cell.ToString();
                        if (colTitle == "装货日期") {
                            indexTakeCntDT_Next = col;
                        }
                        if (colTitle == "托运人") {
                            indexClients_Next = col;
                        }
                        if (colTitle == "装货地点") {
                            indexAddress_Next = col;
                        }
                        if (colTitle == "船名") {
                            indexVsl_Next = col;
                        }
                        if (colTitle == "航次") {
                            indexVoyNumber_Next = col;
                        }
                        if (colTitle == "运单号") {
                            indexBL_Next = col;
                        }
                        if (colTitle == "箱属") {
                            indexBoxAttribute_Next = col;
                        }
                        if (colTitle == "箱型") {
                            indexBoxType_Next = col;
                        }
                        if (colTitle == "箱量") {
                            indexBoxCount_Next = col;
                        }
                        if (colTitle == "货名") {
                            indexGoods_Next = col;
                        }
                        if (colTitle == "货重") {
                            indexGoodsWeight_Next = col;
                        }
                        if (colTitle == "装货事项") {
                            indexLoadMatter_Next = col;
                        }
                        if (colTitle == "特殊事项") {
                            indexSpecialMatter_Next = col;
                        }
                        if (colTitle == "配送优先级")
                        {
                            indexPsyxj_Next = col;
                        }
                    }
                #endregion
                        
            #endregion

            var userId = UserService.GetLoginUser().Id;

            #region 附表装入集合
            var exportupload=new List<OutUploadData>();
            for (int i = 1; i < NextrowCount; i++)
			{

                var r = NextSheet.GetRow(i);
                if (r==null)continue;

                var cell = r.GetCell(indexTakeCntDT_Next);
                if (cell == null || cell.ToString() == "") continue;
                    
			    var pl=new OutUploadData();
                pl.TakeCntDT=NextSheet.GetRow(i).GetCell(indexTakeCntDT_Next).DateCellValue.ToString();
                pl.Clients=NextSheet.GetRow(i).GetCell(indexClients_Next).ToString();
                pl.Address=NextSheet.GetRow(i).GetCell(indexAddress_Next).ToString();
                pl.Vsl=NextSheet.GetRow(i).GetCell(indexVsl_Next).ToString();
                pl.VoyNumber=NextSheet.GetRow(i).GetCell(indexVoyNumber_Next).ToString();
                pl.BLNumber=NextSheet.GetRow(i).GetCell(indexBL_Next).ToString();
                pl.CntOwner=NextSheet.GetRow(i).GetCell(indexBoxAttribute_Next).ToString();
                pl.CntType=NextSheet.GetRow(i).GetCell(indexBoxType_Next).ToString();
                pl.CntCount=NextSheet.GetRow(i).GetCell(indexBoxCount_Next).ToString();
                pl.Goods=NextSheet.GetRow(i).GetCell(indexGoods_Next).ToString();
                pl.GoodsWeight=NextSheet.GetRow(i).GetCell(indexGoodsWeight_Next).ToString();
                pl.LoadMatter=NextSheet.GetRow(i).GetCell(indexLoadMatter_Next).ToString();
                pl.SpecialMatter=NextSheet.GetRow(i).GetCell(indexSpecialMatter_Next).ToString();
                pl.Psyxj=NextSheet.GetRow(i).GetCell(indexPsyxj_Next).ToString();
                exportupload.Add(pl);
			}
            #endregion

            var exportlist = new List<OutConsignation>();
            for (int i = 1; i < rowCount; i++)
            {
                var r = sheet.GetRow(i);
                if (r == null) continue;

                var export = new OutConsignation();
                var exportdetaillist=new List<OutConsignationDetail>();
                export.Id = Guid.NewGuid().ToString();
                export.Clients = sheet.GetRow(i).GetCell(indexClients).ToString();

                var dt = sheet.GetRow(i).GetCell(indexTakeCntDT).DateCellValue.ToString();
                dt = dt.Replace('/', '-');
                export.TakeCntDT = DateTime.Parse(dt).AddHours(23).ToString();
                export.TakeCntDate = sheet.GetRow(i).GetCell(indexTakeCntDT).DateCellValue.ToString();
                export.BizMan = UserService.GetLoginUserName();
                export.BizDT = DateTime.Now.ToShortDateString();
                export.Address=sheet.GetRow(i).GetCell(indexAddress).ToString();
                export.CntOwner=sheet.GetRow(i).GetCell(indexBoxAttribute).ToString();
                export.CntType=sheet.GetRow(i).GetCell(indexBoxType).ToString();
                export.LoadMatter = sheet.GetRow(i).GetCell(indexLoadMatter).ToString();
                export.SpecialMatter = sheet.GetRow(i).GetCell(indexSpecialMatter).ToString();
                export.StarPort = sheet.GetRow(i).GetCell(indexStarPort).ToString();
                export.Psyxj = sheet.GetRow(i).GetCell(indexPsyxj).ToString();

                for (int j = 0; j < exportupload.Count; j++)
                {
                    if (export.TakeCntDate == exportupload[j].TakeCntDT && export.Clients == exportupload[j].Clients && export.Address == exportupload[j].Address && export.CntOwner == exportupload[j].CntOwner && export.CntType == exportupload[j].CntType && export.LoadMatter == exportupload[j].LoadMatter)
                    {
                        var exportdetail=new OutConsignationDetail();
                        exportdetail.Id = Guid.NewGuid().ToString();
                        exportdetail.ParentId = export.Id;
                        exportdetail.Vsl = exportupload[j].Vsl;
                        exportdetail.VoyNumber = exportupload[j].VoyNumber;
                        exportdetail.CntOwner = exportupload[j].CntOwner;
                        exportdetail.CntType1 = exportupload[j].CntType;
                        exportdetail.Address = exportupload[j].Address;
                        exportdetail.BlNumber = exportupload[j].BLNumber;
                        exportdetail.Goods = exportupload[j].Goods;
                        exportdetail.Ton = exportupload[j].GoodsWeight;
                        exportdetaillist.Add(exportdetail);
                    }
                }
                export.Items = exportdetaillist;
                exportlist.Add(export);
            }

            var cc = "";
            WithTransaction(() =>
            {
                foreach (var ic in exportlist)
                {
                    // 检查委托是否已经导入过了
                    var sql = "select count(*) from tms_out_consignation a,tms_out_consignation_detail b where a.id = b.parent_id";
                    sql += "  and CONVERT(varchar(12),f_take_cnt_dt,111)=@takeDt and a.f_clients=@client ";
                    sql += "  and b.f_address=@address and b.f_cnt_type_1=@cnttype and b.f_cnt_owner=@cntowner and a.f_LoadMatter=@LoadMatter";
                    var p = CreateDbParameters();
                    var yy = DateTime.Parse(ic.TakeCntDate).ToString("yyyy/MM/dd");
                    p.AddWithValue("takeDt", yy);
                    p.AddWithValue("client", ic.Clients);
                    p.AddWithValue("address", ic.Address);
                    p.AddWithValue("cnttype", ic.CntType);
                    p.AddWithValue("cntowner", ic.CntOwner);
                    p.AddWithValue("LoadMatter", ic.LoadMatter);
                    var cnt = QueryCount(sql, p);
                    if (cnt > 0)continue;

                    if (ic.Items.Count == 0) continue;

                    //导入基础数据
                    var importService = new ImportService();
                  //  importService.ImportBizMan(ic.BizMan);
                    importService.ImportVsl(ic.Vsl);
                    importService.ImportClients(ic.Clients);

                    var id = IdGeneratorService.NewId();

                    sql = @"insert into tms_out_consignation(id, f_bill_num, 
                                    f_take_cnt_dt, f_clients, f_biz_man,
                                    f_biz_dt, create_user_id, create_datetime,f_LoadMatter,f_SpecialMatter,f_StarPort,f_Psyxj)
                                values (@id, @billNumber,
                                    @takeCntDT, @clients, @bizMan, @bizDT, @userId, getdate(),@LoadMatter,@SpecialMatter,@StarPort,@Psyxj)";
                    p = CreateDbParameters();
                    p.AddWithValue("id",ic.Id);
                    var mid = string.Format("{0:yyMMdd}", DateTime.Now);
                    var billNumber = new BillNumberService().GetAutoNumber("出口委托", "CK", mid);
                    p.AddWithValue("billNumber", billNumber);
                  //  var dt = ConverDT(ic.TakeCntDT);
                    p.AddWithValue("takeCntDT", string.IsNullOrEmpty(ic.TakeCntDT) ? null : ic.TakeCntDT);
                    p.AddWithValue("clients", ic.Clients);
                    p.AddWithValue("bizMan", ic.BizMan);
                    p.AddWithValue("bizDT", string.IsNullOrEmpty(ic.BizDT) ? null : ic.BizDT);
                    p.AddWithValue("userId", userId);
                    p.AddWithValue("LoadMatter", string.IsNullOrEmpty(ic.LoadMatter) ? "" : ic.LoadMatter);
                    p.AddWithValue("SpecialMatter", string.IsNullOrEmpty(ic.SpecialMatter) ? "" : ic.SpecialMatter);
                    p.AddWithValue("StarPort", string.IsNullOrEmpty(ic.StarPort) ? "" : ic.StarPort);
                    p.AddWithValue("Psyxj", string.IsNullOrEmpty(ic.Psyxj) ? "" : ic.Psyxj);

                    Execute(sql, p);

                    foreach (var icd in ic.Items)
                    {
                        //导入基础数据
                       // importService.ImportCntOwner(icd.CntOwner);
                       // importService.ImportCntOwner(icd.GoodsOwner);
                        importService.ImportAddress(icd.Address);

                        sql = @"insert into tms_out_consignation_detail(
                                    id, parent_id,f_vsl,f_voy_num,f_cnt_owner,
                                    f_cnt_type_1,f_address, f_bl_number,
                                    f_goods,create_user_id, create_datetime,f_ton)
                                values(@id, @parentId,@vsl,@voynum,@cntOwner,
                                        @cnttype,@address, @blNumber,
                                        @goods, @userId, getdate(),@ton)";       
                        p = CreateDbParameters();
                        p.AddWithValue("id", icd.Id);
                        p.AddWithValue("parentId", icd.ParentId);
                        p.AddWithValue("vsl", icd.Vsl);
                        p.AddWithValue("voynum", icd.VoyNumber);
                        p.AddWithValue("cntOwner", icd.CntOwner);
                        p.AddWithValue("cnttype", icd.CntType1);
                        p.AddWithValue("goods", icd.Goods);
                        p.AddWithValue("address", icd.Address);
                        p.AddWithValue("blNumber", icd.BlNumber);
                        p.AddWithValue("userId", userId);
                        p.AddWithValue("ton", icd.Ton);

                        Execute(sql, p);
                    }
                }
            });

            var result = new Result { Success = true };

            return JsonConvert.SerializeObject(result);
        }

        /// <summary>
        /// 把 "dd/mm/yyyy hh:mm"转换为 "yyyy-mm-dd hh:mm"
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        private string ConverDT(string dt) {
            if (string.IsNullOrEmpty(dt)) {
                return string.Empty;
            }

            var dd = string.Empty;
            var mm = string.Empty;
            var yyyy = string.Empty;
            var h = string.Empty;
            var m = string.Empty;

            var data = dt.Split(' ');
            if (data.Length == 1) {
                h = "00";
                m = "00";
            } else {
                var hmArray = data[1].Split(':');
                h = hmArray[0];
                m = hmArray[1];

                var ymdArray = data[0].Split('/');
                yyyy = ymdArray[2];
                mm = ymdArray[1];
                dd = ymdArray[0];
            }

            return yyyy + "-" + mm + "-" + dd + " " + h + ":" + m;
        }

        public Result CancelPlan(string id) {
            // 检查是否能取消运输
            var sql = @"select count(*) from tms_out_consignation_detail
                        where (parent_id = @id) and (f_status is not null) and (f_status <> '待下达计划')";
            var p = CreateDbParameters();
            p.AddWithValue("id", id);
            var cnt = QueryCount(sql, p);
            if (cnt > 0) {
                return new Result { Success = false, Messgae = "送货明细已经被下达计划，不能取消运输" };
            }

            WithTransaction(() => {
                sql = @"
                        update tms_out_consignation_detail
                        set f_status = null, f_plan_act_dt = null
                        where parent_id = @id

                        update tms_out_consignation
                        set f_status = null, f_confirm_user_name = null, 
                            f_confirm_dt = null, f_plan_act_dt = null
                        where id = @id
                    ";
                p = CreateDbParameters();
                p.AddWithValue("id", id);
                Execute(sql, p);
            });

            return new Result { Success = true };
        }
    }
}